package xin.nick.demo;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

public class ProcessExcel {

    public static void main(String[] args) throws IOException {
//        String filePath1 = "E:\\temp\\20210319\\70.xlsx";
//        ArrayList<String> list1 = load1Excel(filePath1);
//        String filePath2 = "E:\\temp\\20210319\\72.xlsx";
//        HashMap<String,String> map2 = load2Excel(filePath2);

        ArrayList<String[]> exportList = new ArrayList<String[]>();

//        // 遍历 1
//        for(String name : list1) {
//            String value = map2.get(name);
//            if(value != null) {
//                String[] ss = new String[2];
//                ss[0] = name;
//                ss[1] = value;
//                exportList.add(ss);
//            }
//        }


        String filePath3 = "E:\\temp\\20210319\\82.xlsx";
        HashMap<String,String> map3 = load2Excel(filePath3);
        String filePath4 = "E:\\temp\\20210319\\81.xlsx";
        HashMap<String,String> map4 = load2Excel(filePath4);


        for(String key : map3.keySet()) {
            String value = map3.get(key);
            if(value != null && key != null) {
                String[] ss = new String[2];
                ss[0] = key;
                ss[1] = value;
                exportList.add(ss);
            }
        }
        for(String key : map4.keySet()) {
            String value = map4.get(key);
            if(value != null && key != null) {
                String[] ss = new String[2];
                ss[0] = key;
                ss[1] = value;
                exportList.add(ss);
            }
        }

        exportExcel("E:\\temp\\20210319\\83.xlsx", exportList);
    }


    private static ArrayList<String> load1Excel(String filePath) throws IOException {
        ArrayList<String> resultList = new ArrayList<String>();

        System.out.println("加载excel1...");
        Long start = System.currentTimeMillis();

        File xlsFile = new File(filePath);

        // 工作表
        Workbook workbook = WorkbookFactory.create(xlsFile);
        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        // 遍历表。
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 行数。
            int rowNumbers = sheet.getLastRowNum() + 1;
            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }
            int cells = temp.getPhysicalNumberOfCells();
            // 读数据。
            for (int row = 0; row < rowNumbers; row++) {
                Row r = sheet.getRow(row);
                for (int col = 0; col < cells; col++) {
                    if(r != null) {
                        if(r.getCell(col) != null) {
                            String value = r.getCell(col).toString();
                            resultList.add(value);
                        }
                    }

                }

            }
        }

        long total = System.currentTimeMillis() - start;
        System.out.println("耗时: " + total);
        System.out.println("加载excel1完成");
        return resultList;
    }

    static HashMap<String,String> load2Excel(String filePath) throws IOException {
        HashMap<String, String> resultMap = new HashMap<String, String>(777777);

        System.out.println("加载excel2..." + filePath);
        Long start = System.currentTimeMillis();

        File xlsFile = new File(filePath);

        // 工作表
        Workbook workbook = WorkbookFactory.create(xlsFile);
        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        // 遍历表。
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 行数。
            int rowNumbers = sheet.getLastRowNum() + 1;
            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }
            int cells = temp.getPhysicalNumberOfCells();
            // 读数据。
            for (int row = 0; row < rowNumbers; row++) {
                Row r = sheet.getRow(row);

                HashMap<String, String> m = new HashMap<String, String>(2);
                for (int col = 0; col < cells; col++) {

                    if(r != null) {
                        if(r.getCell(col) != null) {
                            String value = r.getCell(col).toString();

                            if(col == 1) {
                                m.put("mobile", value);
                            }
                            if(col == 0) {
                                m.put("plat", value);
                            }
                        }
                    }
                }

                String key = m.get("plat");
                String value = m.get("mobile");
                if(key != null && value != null) {
                    resultMap.put(key, value);
                }

            }
        }

        long total = System.currentTimeMillis() - start;
        System.out.println("耗时: " + total);
        System.out.println("加载excel2完成");

        return resultMap;
    }

    private static void exportExcel(String filePath, ArrayList<String[]> exportList) throws IOException {
        //创建工作薄对象
        //这里也可以设置sheet的Name
        XSSFWorkbook workbook=new XSSFWorkbook();
        //创建工作表对象
        XSSFSheet sheet = workbook.createSheet();

        //创建工作表的行
        int len = exportList.size();
        for(int i = 0; i < len; i++) {
            XSSFRow row = sheet.createRow(i);
            String[] ss = exportList.get(i);

            row.createCell(1).setCellValue(ss[1]);
            row.createCell(0).setCellValue(ss[0]);
        }

        //设置sheet的Name
        workbook.setSheetName(0,"车牌-手机");

        //文档输出
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);
        out.close();

    }
}
